﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
//using PurchaseManagementSystem.DBModel;


namespace PurchaseManagementSystem
{
    public static class DBServer
    {
        static string sql;
        public static DataTable GetOrderlike(string oid)
        {

            sql = string.Format("select *" + " from db_final_order where Order_num like '%{0}%'",oid.Trim());
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }
    /// <summary>
    /// 查找所有订单项，陈少喆
    /// </summary>
    /// <returns></returns>
        public static DataTable GetOrder()
        {

            sql = string.Format("select *" + " from db_init_order" );
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }
        public static DataTable GetnoPassOrder()
        {

            sql = string.Format("select *" + " from db_nopassed_products_list");
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }
         /// <summary>
        /// 根据Productid返回库存,陈少喆
         /// </summary>
         /// <param name="Productid"></param>
         /// <returns></returns>
        public static int GetBalanceByProductid(int Productid)
        {
            sql = string.Format("select count(Product_balance)" + " from db_product_information" + " where Product_id= '{0}'", Productid);
            int amount = DBHelper.GetScalar(sql);
            if (amount > 0)                                       //查到信息已入库，则返回存量
            {
                sql = string.Format("select Product_balance" + " from db_product_information" + " where Product_id= '{0}'", Productid);
                return DBHelper.GetScalar(sql);
            }
            else                                             //信息未出现过，返回-1
            {
                return -1;
            }
        }
        /// <summary>
        /// 从passed表中抽取，陈少喆
        /// </summary>
        /// <param name="Orderid"></param>
        /// <param name="Productid"></param>
        /// <returns></returns>
        public static DataTable GetPassedOrderByOid_Pid(int Orderid, int Productid)
        {

            sql = string.Format("select *" + " from db_passed_products_list" + " where Record_id= '{0}' and Product_id='{1}'", Orderid, Productid);
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }
       /// <summary>
       /// 根据Orderid返回Datatable,陈少喆
       /// </summary>
       /// <param name="Orderid"></param>
       /// <returns></returns>
        public static DataTable GetOrderByOid_Pid(int Orderid,int Productid)
        {
           
            sql = string.Format("select *" + " from db_init_order" + " where Order_num= '{0}' and Product_id='{1}'", Orderid,Productid);
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }
        /// <summary>
        /// 将仓库存货量小于20的商品存入通过订单表，陈少喆
        /// </summary>
        public static void Insertinto_pass(int re_id, int em_id, DateTime time, int pr_id,int pr_num)
        {
            //sql = string.Format ("insert into db_passed_products_list (select * from db_init_order where Order_num = '{0}')" , Orderid );
            sql = "insert into db_passed_products_list values('" + re_id + "','" + em_id + "','" + pr_id + "','" + time.ToString("yyyy-MM-dd") + "','" + pr_num + "')";
            DBHelper.ExecuteCommand(sql);
            
        }
        /// <summary>
        /// 将仓库存货量小于20的商品存入通过订单表，陈少喆
        /// </summary>
        public static void Insertinto_nopass(int re_id,int em_id,DateTime time,int pr_id,int pr_num,string reason)
        {
            /*将足够的订单项存入nopassed数据库，理由一项填写reason*/
            sql = "insert into db_nopassed_products_list values('" + re_id + "','" + em_id + "','" + reason + "','" + time.ToString("yyyy-MM-dd") + "','" + pr_id + "')";
           // sql = string.Format("insert into db_nopassed_products_list (select Order_num,Employee_id,'Enough',Order_time,Product_id from db_init_order where Order_num = '{0}')", Orderid);
            DBHelper.ExecuteCommand(sql);
        }

        /// <summary>
        /// 仓库部提交订单__王佳
        /// </summary>
        /// <param name="Orderid"></param>
        /// <param name="Employeeid"></param>
        /// <param name="Comid"></param>
        /// <param name="Ordertime"></param>
        /// <param name="Comnum"></param>
        public static void Insertinto_initOrder(int Orderid,int Employeeid,int Comid,DateTime Ordertime,int Comnum)
        {
            string strSql = "insert into db_init_order values(" + Orderid + "," + Employeeid + "," + Comid + "," +"getdate()" + "," + Comnum + ")";
            DBHelper.ExecuteCommand(strSql);

        }

        /// <summary>
        /// 竞标系统，查找库存中是否有该类商品__王佳
        /// </summary>
        /// <param name="ProID"></param>
        /// <param name="ProBal"></param>
        /// <param name="ProName"></param>
        /// <param name="Supplier"></param>
        /// <param name="ProPrice"></param>
        public static void SearchOrderID(int ProID, int ProBal, string ProName, string Supplier, int ProPrice)
        {
            string sqlFind = "select * from db_product_information where Product_ID = '" + ProID + "'";
            DBHelper.ExecuteCommand(sqlFind);
            string sqlCount = "select count(*) from db_product_information where Product_ID = '" + ProID + "'";
            
            //DBHelper.ExecuteCommand(sqlCount);
            int count = DBHelper.GetScalar(sqlCount);

            //bool flag = DBHelper.Exists(sqlFind );
            //if (flag == true)
            if(count > 0)
            {
                DBServer.BidOrder(ProID, ProBal, ProName, Supplier, ProPrice);
            }

           // else if(flag == false)
            else
            {
                DBServer.BidNullOrder(ProID, ProBal, ProName, Supplier, ProPrice);
            }

        }

        /// <summary>
        /// 竞标系统，添加新订单__王佳
        /// </summary>
        /// <param name="ProID"></param>
        /// <param name="ProBal"></param>
        /// <param name="ProName"></param>
        /// <param name="Supplier"></param>
        /// <param name="ProPrice"></param>
        public static void BidNullOrder(int ProID, int ProBal, string ProName, string Supplier, int ProPrice)
        {
            string strSql = "insert into db_product_information values('" + ProID + "','" + ProName.ToString()+ "','" + ProPrice + "','" + ProBal + "','" + Supplier.ToString() + "')";
            DBHelper.ExecuteCommand(strSql);
        }

        /// <summary>
        /// 竞标系统，根据最低价更新商品信息__王佳
        /// </summary>
        /// <param name="ProID"></param>
        /// <param name="ProBal"></param>
        /// <param name="ProName"></param>
        /// <param name="Supplier"></param>
        /// <param name="ProPrice"></param>
        public static void BidOrder(int ProID, int ProBal, string ProName, string Supplier, int ProPrice)
        {
            sql = "select Product_price from db_product_information where Product_ID = '"+ ProID+"'";
            string oldPrice = DBHelper.GetScalarString(sql);
            MessageBox.Show("历史最低价格" + "        " + oldPrice + Environment.NewLine
                          + "当前商家价格" + "        " + ProPrice .ToString ());

            int old = int.Parse(oldPrice); 
            if ( old > ProPrice)
            {
                string strSql = "update [db_product_information] set Product_price = '" + ProPrice + "' where Product_id = '"+ ProID  +"' ";
                DBHelper.ExecuteCommand(strSql);
            }
            
            MessageBox.Show("竞标成功价格" + "        " + DBHelper.GetScalarString(sql));     
        }




        /// <summary>
        /// 根据OrderId返回最终订单FinalOrder，赵磊
        /// </summary>
        /// <param name="Orderid"></param>
        /// <returns></returns>
        public static DataTable GetFinalOrderByOrderid(int Orderid)
        {
          //  sql = "select* " + "from db_final_order" + Orderid;
            sql = string.Format("select *" + " from db_final_order" + " where Order_num= '{0}'", Orderid);
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }

        /// <summary>
        /// 根据RecordId返回通过商品列表，赵磊
        /// </summary>
        /// <param name="Orderid"></param>
        /// <returns></returns>
        public static DataTable GetPassedProductListByOrderid(int Recordid)
        {
            //sql = "select* " + "from db_passed_products_list" + Recordid;
            sql = string.Format("select *" + " from db_passed_products_list" + " where Record_id= '{0}'", Recordid);
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }

        /// <summary>
        /// 在finalorder中插入记录，赵磊
        /// </summary>
        /// <param name="Orderid"></param>
        public static void Insertinto_final_order(int re_id, int em_id, int pr_id, DateTime time, int pr_num, int tot_price)
        {
           // sql = "insert into db_final_order select * from db_passed_products_list" + Orderid;
            sql = "insert into db_final_order values('" + re_id + "','" + em_id + "','" + pr_id + "','" + time.ToString("yyyy-MM-dd") + "','" + pr_num + "','" + tot_price + "')";
            DBHelper.ExecuteCommand(sql);
        }

        /// <summary>
        /// 删除passed表里筛选出来的商品记录，赵磊
        /// </summary>
        /// <param name="re_id"></param>
        /// <param name="pr_id"></param>

        public static void Deletefrom_passed_products_list(int pr_id,  int re_id)
        {
            sql = string.Format("delete from db_passed_products_list where Product_id = '{0}' and Record_id='{1}'", pr_id, re_id);
            DBHelper.ExecuteCommand(sql);
        }
        /// <summary>
        /// 根据product_id返回对应商品的价格，赵磊
        /// </summary>
        /// <param name="product_id"></param>
        /// <returns></returns>
        public static DataTable GetPassedProductByProductId(int product_id)
        {
            sql = string.Format("select *" + " from db_product_information" + " where Product_id= '{0}'", product_id);
            DataTable dt = DBHelper.GetDataSet(sql);
            return dt;
        }
        /// <summary>
        /// 根据用户名，密码返回员工的部门,孙长晖
        /// </summary>
        /// <param name="useraccount"></param>
        /// <param name="usepwd"></param>
        /// <returns></returns>
        public static int Find_department(string useraccount, string userpwd)
        {
            StringBuilder sqlLoginId = new StringBuilder(); // 查询用户名的SQL语句
            StringBuilder sqlLoginPwd = new StringBuilder();// 查询密码的SQL语句

            sqlLoginId.AppendFormat("SELECT COUNT(*) FROM [db_employee] WHERE [Employee_loginaccount]='{0}'", useraccount);
            sqlLoginPwd.AppendFormat("SELECT Employee_department FROM [db_employee] WHERE [Employee_loginaccount]='{0}' AND [Employee_password]='{1}'",
                useraccount, userpwd);
            int count = 0;
            count = DBHelper.GetScalar(sqlLoginId.ToString());
            if (count > 0)//用户名存在
            {
                SqlDataReader dr = DBHelper.GetReader(sqlLoginPwd.ToString());
                if(dr.Read())
                {
                    count = (int)dr["Employee_department"];
                    dr.Close();
                    return count;
                }
                else
                {
                    dr.Close();
                    return -1;//密码错误
                }
            }
            else
            {
                 return 0;//用户名不存在
            }
        }
    }

}
